package ibms_rcp.util;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.BillLog;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

/*
 * SqlUtil类 是一些本系统常用的，而且重复性很大一些基础sql操作,此外还包含一些经常使用的消防法，
 * 如获取当前系统时间戳等
 * 独立成为一个工具类，方便复用
 * 该类的每个方法均有注释，表明该方法的作用
 */
public class SqlUtil {
	/*
	 * 遍历 m2mnap_moi表，依据公司的moc_id，查找到所有公司实例的moi_id
	 */
	public static ArrayList<Long> getAllCompanyMoi_id() {
		ArrayList<Long> allCompanyMois = new ArrayList<Long>();
		Connection conn = DataBaseManager.getConnection();
		try {
			String sqlString = "select id from m2mnap_moi where moc_id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				allCompanyMois.add(rs.getLong(1));
				System.out.println(rs.getLong(1));
			}
			rs.close();
			pstmt.close();
			conn.commit();

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		return allCompanyMois;
	}

	/*
	 * 根据业主公司的moi_id，查找该公司所占有的全部房间的moi_id集合。
	 */
	public static ArrayList<Long> getAllRoomMoi_id(Long company_moi_id){
		ArrayList<Long> room_moi_ids = new ArrayList<Long>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select a_moi_id from m2mnap_moi_rs "
					+ "where a_moc_id =? and z_moc_id=? and z_moi_id =?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, Constants.MOC_ROOM_ID);
			pstmt.setLong(2, Constants.MOC_COMPANY_ID);
			pstmt.setLong(3, company_moi_id);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				room_moi_ids.add(rs.getLong(1));
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return room_moi_ids;
	}
	/*
	 * 根据房间的moi_id,查找这个房间的电表moi_id集合
	 */
	public static ArrayList<Long> getAllAmmeterMoi_id(Long room_moi_id){
		ArrayList<Long> allAmmeterMoi_id = new ArrayList<Long>();
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select a_moi_id from m2mnap_moi_rs "
					+ "where a_moc_id =? and z_moc_id=? and z_moi_id =?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, Constants.MOC_Ammeter_ID);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			pstmt.setLong(3, room_moi_id);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				allAmmeterMoi_id.add(rs.getLong(1));
				System.out.println(rs.getLong(1));
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return allAmmeterMoi_id;
	}
	
	/*
	 * 由给定的楼号，楼层好，房间号，获取该房间的moi_id,以供后续查找之用特别说明
	 * 在使用该方法时，需要提前处理 从图形获得的变量。因为从图形界面获得是的String为
	 * 楼1，楼层1101，房间1101等，需要把楼，楼层，房间等汉字去掉方可处理。
	 * 如：buildingId = buildingId.substring(1);floorId = floorId.substring(2);
	 * roomId = roomId.substring(2);
	 */
	public static Long getRoomMoi_id(String buildingId, String floorId,
			String roomId) {
		Connection conn = DataBaseManager.getConnection();
		Long room_moi_id = null;
		try {
			conn.setAutoCommit(false);
			String sqlString = "select moi_id from m2mnap_moi_attribute where Id = ? and "
					+ "buildingId = ? and floorId =?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, Long.valueOf(roomId));
			pstmt.setLong(2, Long.valueOf(buildingId));
			pstmt.setLong(3, Long.valueOf(floorId));

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				room_moi_id = rs.getLong(1);
			}
			rs.close();
			pstmt.close();
			conn.commit();
			System.out.println(room_moi_id);

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return room_moi_id;
	}
	/*
	 * 根据roommoi,找到该房间的楼，楼层，及房间号信息，返回一个size=3的字符串数组，
	 * 依次分别表示楼，楼层，房间，例如：楼2，楼层2001，房间2101
	 */
	public static String[] getBuFlRoInfo(Long roommoi) {
		Connection conn = DataBaseManager.getConnection();
		String buflroinfo[]= new String[3];
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute where moi_id = ? and moc_attribute_code = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, roommoi);
			pstmt.setString(2, "buildingId\n");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				buflroinfo[0] = rs.getString(1);
			}
			
			pstmt.setString(2, "floorId");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				buflroinfo[1] = rs.getString(1);
			}
			
			pstmt.setString(2, "Id");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				buflroinfo[2] = rs.getString(1);
			}
			
			rs.close();
			pstmt.close();
			conn.commit();
			//System.out.println(buflroinfo);

		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return buflroinfo;
	}
	
	/*
	 * 由业主公司的moi_id 获取业主公司的名称
	 */
	public static String getCompanyNamebyCompanyMoi_id(Long company_moi_id) {
		Connection conn = DataBaseManager.getConnection();
		String company_name = null;
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute " +
					"where moi_id = ? and moc_attribute_code = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, company_moi_id);
			pstmt.setString(2, "name");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				company_name = rs.getString(1);
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return company_name;
	}
	
	/*
	 * 有业主公司的编号code，获取业主公司的名称,估计本方法用处不大
	 */
	public static String getCompanyNamebyCompanyCode(String code) {
		Connection conn = DataBaseManager.getConnection();
		String company_name = null;
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute " +
					"where moc_attribute_code = ? and moi_id in " +
					"(select moi_id from m2mnap_moi_attribute " +
					"where moc_id = ? and moc_attribute_code = ? and new_value = ?)";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setString(1, "name");
			pstmt.setLong(2, Constants.MOC_COMPANY_ID);
			pstmt.setString(3, "Id");
			pstmt.setString(4, code);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				company_name = rs.getString(1);
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return company_name;
	}
	
	/*
	 * 由业主公司的moi_id 获取业主公司的余额
	 */
	public static double getBalancebyCompanyMoi_id(Long company_moi_id) {
		Connection conn = DataBaseManager.getConnection();
		double balance = 0;
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute " +
					"where moi_id = ? and moc_attribute_code = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, company_moi_id);
			pstmt.setString(2, "bookSaving");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				balance = rs.getDouble(1);
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return balance;
	}
	
	//根据房间moi_id,查看某房间是否开通了预收费功能
	public static boolean isBookFunction(Long room_moi_id){
		boolean isBookFunction = false;
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select new_value from m2mnap_moi_attribute " +
					"where moi_id = ? and moc_attribute_code = ?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, room_moi_id);
			pstmt.setString(2, "bookFunction");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				String m = rs.getString(1);
				//System.out.println("----"+m);
				if(m.equalsIgnoreCase("true")){
					isBookFunction = true;
				}else{
					isBookFunction = false;
				}
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return isBookFunction;
	}
	
	//获得系统当前 时间戳
	public static Timestamp getTimeStamp(){
		Date myDate = new Date();
		long time = myDate.getTime();
		//System.out.println(time);
		return (new Timestamp(time));
		
	}
	//根据某一个房间的moi_id,查找该房间对应的业主公司的moi_id
	public static Long getCompanyMoi_id(Long room_moi_id){
		Long company_moi_id = null;
		Connection conn = DataBaseManager.getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlString = "select a_moi_id from m2mnap_moi_rs "
					+ "where a_moc_id =? and z_moc_id=? and z_moi_id =?";
			PreparedStatement pstmt = conn.prepareStatement(sqlString);
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			pstmt.setLong(3, room_moi_id);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				company_moi_id = rs.getLong(1);
			}
			rs.close();
			pstmt.close();
			conn.commit();
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return company_moi_id;
	}
	
	public static void main(String args[]){

		//System.out.println(SqlUtil.getBuFlRoInfo(4823L)[0]);
		/*BillLog aBillLog = new BillLog();
		A(aBillLog);
		System.out.println(aBillLog.getCompany_name());*/
	}
	/*public static BillLog A(BillLog a){
		a.setCompany_name("123");
		return a;
	}*/

}
